﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace MusicDatabase
{
    /*
     * Auteur:  Joris Molnár en Niels van Leusden
     * Datum:   31-1-2013
     * Omschrijving: In deze klasse wordt de connectie gemaakt tussen AddSong en de Database via SQLservices.
     *               Haalt gegevens op die bij een song horen om te laten zien in bijvoorbeeld AlbumInfo.
     *               En maakt het mogelijk om nieuwe songs te maken en oude te verwijderen.
     * 
     */
    public class Song
    {
        private SQLServices sqlService;
        private DataMode dataMode;

        private int songID;
        private string name;
        private int artistID;
        private string genreID;
        private int length;
        private int date;
        private int trackNr;

        public SQLServices SqlService
        {
            get
            {
                return sqlService;
            }
            set
            {
                this.sqlService = value;
            }
        }

        public Song()
        {
            songID = -1;
        }

        /// <summary>
        /// Met deze constructor wordt een nieuw medium gemaakt met data afkomstig van de user.
        /// </summary>
        /// <param name="author"></param>
        /// <param name="mediumData"></param>
        public Song(string[] songData)
        {
            this.dataMode = DataMode.toevoegen;
            this.songID = -1;
            this.name = songData[0].Replace("'", "''");
            this.artistID = Convert.ToInt32(songData[1]);
            if (songData[2] == "-1")
            {
                this.genreID = "NULL";
            }
            else
            {
                this.genreID = songData[2];
            }
            this.length = Convert.ToInt32(songData[3]);
            this.date = Convert.ToInt32(songData[4]);
            this.trackNr = Convert.ToInt32(songData[5]);
        }

        public string[] GetDetails()
        {
            return new string[] { Convert.ToString(songID), name, Convert.ToString(this.artistID), Convert.ToString(genreID), Convert.ToString(length), Convert.ToString(date) };
        }

        public string[] GetKolomNamen()
        {
            return new string[] { "SongID", "Title", "ArtistID", "GenreID", "Length", "Date" };
        }

        public string SelectCommand(string tabelnaam, string criteria)
        {
            return "SELECT * FROM " + tabelnaam + " " + criteria;
        }

        public string InsertCommand(string tabelnaam, string veldnamen, string velden)
        {
            return "INSERT INTO " + tabelnaam + "  (" + veldnamen + ") VALUES (" + velden + ") ";
        }

        private string veldnamen = "song_name, art_id, song_genre, song_length, song_date";

        /// <summary>
        /// Save-method slaat alleen "nieuwe" song op.
        /// </summary>
        public void Save(int albumID)
        {
            if (sqlService == null) sqlService = new SQLServices();


            string velden;

            if (dataMode == DataMode.toevoegen && !this.Exist(sqlService))
            {
                // let op: stringvelden moeten ''-bevatten en int-velden NIET!
                velden = "'" + name.Replace("'", "''") + "'," + this.artistID + "," + genreID + "," + length + "," + date;
                string command = InsertCommand("SONG", veldnamen, velden);
                sqlService.Insert(command);

                // songID ophalen
                if (genreID == "NULL")
                {
                    command = "SELECT song_id FROM song " +
                    "WHERE song_name = '" + name.Replace("'", "''") + "' AND art_id = " + artistID + " AND song_genre IS NULL AND song_length = " +
                    length + " AND song_date = " + date;
                }
                else
                {
                    command = "SELECT song_id FROM song " +
                        "WHERE song_name = '" + name.Replace("'", "''") + "' AND art_id = " + artistID + " AND song_genre = " + genreID + " AND song_length = " +
                        length + " AND song_date = " + date;
                }
                List<string[]> songTable = sqlService.GetTable(command);
                if (songTable.Count == 2 && songID == -1)
                {
                    songID = Convert.ToInt32(songTable[1][0]);
                }

                // koppelen aan het album met songrelease
                veldnamen = "song_id, alb_id, song_alb_track";
                velden = songID + "," + albumID + "," + trackNr;
                command = InsertCommand("SONGRELEASE", veldnamen, velden);
                sqlService.Insert(command);
            }
            else if (dataMode == DataMode.toevoegen && this.Exist(sqlService)) // Als de song al bestaat koppelen in songrelease aan het tweede album
            {
                // songID ophalen
                string command = "SELECT song_id FROM song " +
                    "WHERE song_name = '" + name.Replace("'", "''") + "' AND art_id = " + artistID;

                List<string[]> songTable = sqlService.GetTable(command);
                if (songTable.Count == 2 && songID == -1)
                {
                    songID = Convert.ToInt32(songTable[1][0]);
                }

                // koppelen aan het album met songrelease
                veldnamen = "song_id, alb_id, song_alb_track";
                velden = songID + "," + albumID + "," + trackNr;
                command = InsertCommand("SONGRELEASE", veldnamen, velden);
                sqlService.Insert(command);
            }
            this.dataMode = DataMode.raadplegen;
        }

        /// <summary>
        /// raadpleegt het record op basis van identificerende gegevens
        /// "AuthorID" en "Titel"
        /// </summary>
        /// <param name="sqlService"></param>
        /// <returns></returns>
        private bool Exist(SQLServices sqlService)
        {
            string command = SelectCommand("SONG", " WHERE art_id = " + this.artistID + " AND song_name = '" + this.name.Replace("'", "''") + "'");
            return (sqlService.Exist(command));
        }

        /// <summary>
        /// Haalt de author-data op uit de database.
        /// </summary>
        /// <param name="authorID"> uniek id van de author</param>
        /// <returns>string[] met de authordata</returns>
        private string[] GetArtist(int artistID)
        {
            SQLServices sqlService = new SQLServices();
            string command = SelectCommand("ARTIST", " WHERE art_id = " + artistID);
            return sqlService.GetTable(command)[1];
        }

        public void DeleteSongs(List<int> songID)
        {
            if (songID.Count() == 0) return;

            SQLServices sqlService = new SQLServices();
            string command = "DELETE FROM song " +
                "WHERE song.song_id = " + songID[0];

            for (int i = 1; i < songID.Count(); i++)
            {
                command = command + " OR song.song_id = " + songID[i];
            }

            sqlService.Delete(command);
        }
    }
}
